﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ViewModels;

namespace DAL
{
    public class BookDAL
    {
        SqlCommand cmd;//定义Command对象
        DataSet ds;//定义数据集对象
        public BookDAL()
        {
            cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;//定义DB访问类型为存储过程
            ds = new DataSet();
        }
        public DataSet GetBookList()
        {
            cmd.CommandText = "GetBooks";
            cmd.Parameters.Clear();//清除传参内容
            ds = DBHelper.QueryData(cmd);//调用通用数据库访问类实现DataSet读取
            return ds;
        }

        public DataSet GetBookListByBookID(string inputString)
        {
            cmd.CommandText = "GetBooksByBookID";
            cmd.Parameters.Clear();//清除传参内容
            cmd.Parameters.Add("@bookid", SqlDbType.VarChar, 20).Value = inputString;
            ds = DBHelper.QueryData(cmd);//调用通用数据库访问类实现DataSet读取
            return ds;
        }
        public DataSet GetBookListByBookName(string inputString)
        {
            cmd.CommandText = "GetBookListByBookName";
            cmd.Parameters.Clear();//清除传参内容
            cmd.Parameters.Add("@bookname", SqlDbType.NVarChar, 200).Value = inputString;
            ds = DBHelper.QueryData(cmd);//调用通用数据库访问类实现DataSet读取
            return ds;
        }
        public DataSet GetBookListByPublisher(string inputString)
        {
            cmd.CommandText = "GetBookListByPublisher";
            cmd.Parameters.Clear();//清除传参内容
            cmd.Parameters.Add("@publisher", SqlDbType.NVarChar, 50).Value = inputString;
            ds = DBHelper.QueryData(cmd);//调用通用数据库访问类实现DataSet读取
            return ds;
        }

        public DataSet GetBookListByAuthor(string inputString)
        {
            cmd.CommandText = "GetBookListByAuthor";
            cmd.Parameters.Clear();//清除传参内容
            cmd.Parameters.Add("@author", SqlDbType.NVarChar, 50).Value = inputString;
            ds = DBHelper.QueryData(cmd);//调用通用数据库访问类实现DataSet读取
            return ds;
        }
        public DataSet GetBookClassList()
        {
            cmd.CommandText = "GetBookClassList";
            cmd.Parameters.Clear();//清除传参内容
            ds = DBHelper.QueryData(cmd);//调用通用数据库访问类实现DataSet读取
            return ds;
        }
        /// <summary>
        /// 对新增书籍的查重（查找重复记录）
        /// </summary>
        /// <param name="vm">BookVM</param>
        /// <returns>true:有重复记录；false:没有记录</returns>
        public bool BookDuplicateChecking(BookVM vm)
        {
            cmd.CommandText = "GetBookByBookID";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@bookid", SqlDbType.VarChar, 20).Value = vm.BookID;
            object obj = DBHelper.GetScalar(cmd);
            if (obj != null)
                return true;
            else
                return false;
        }
        public object EditBook(BookVM vm, string operation)
        {
            if (operation == "Insert")
                cmd.CommandText = "InsertBook";
            if (operation == "Update")
                cmd.CommandText = "UpdateBook";

            cmd.Parameters.Clear();//清除传参内容

            cmd.Parameters.Add("@BookID", SqlDbType.VarChar, 20).Value = vm.BookID;
            cmd.Parameters.Add("@BookName", SqlDbType.NVarChar, 200).Value = vm.BookName;
            cmd.Parameters.Add("@ISBN", SqlDbType.Char, 20).Value = vm.ISBN;
            cmd.Parameters.Add("@Author", SqlDbType.NVarChar, 50).Value = vm.Author;
            cmd.Parameters.Add("@PublishDate", SqlDbType.DateTime).Value = vm.PublishDate;
            cmd.Parameters.Add("@BookVersion", SqlDbType.NVarChar, 40).Value = vm.BookVersion;
            cmd.Parameters.Add("@WordCount", SqlDbType.Int).Value = vm.WordCount;
            cmd.Parameters.Add("@PageCount", SqlDbType.Int).Value = vm.PageCount;
            cmd.Parameters.Add("@Publisher", SqlDbType.NVarChar, 50).Value = vm.Publisher;
            cmd.Parameters.Add("@Prices", SqlDbType.Money).Value = vm.Price;
            cmd.Parameters.Add("@ClassID", SqlDbType.VarChar, 10).Value = vm.BookClassesVM.ClassID;

            try
            {
                int num = DBHelper.ExecuteSQL(cmd);
                return num;
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 根据索书号查找匹配书籍信息
        /// </summary>
        /// <param name="bookid">索书号</param>
        /// <returns>一行数据</returns>
        public DataSet GetBookByBookID(string bookid)
        {
            cmd.CommandText = "GetBookByBookID";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@bookid", SqlDbType.VarChar, 20).Value = bookid;
            DataSet ds = DBHelper.QueryData(cmd);
            return ds;
        }
        public bool DelBookByID(string bookid)
        {
            cmd.CommandText = "DeleteBookByBookID";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@bookid", SqlDbType.VarChar, 20).Value = bookid;
            try
            {
                int num = DBHelper.ExecuteSQL(cmd);
                if (num > 0)
                    return true;
                else
                    return false;
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }
    }
}
